package com.manage.service;

import java.util.List;

import org.apache.log4j.Logger;

import com.manage.config.Config;
import com.manage.model.ProductCodeInfo;
import com.manage.model.ProductCodeProvince;
//import com.manage.model.vo.IProductCode;
import com.service.AbstractService;
import com.util.ConfigUtil;
import com.utils.dbcp.ConnectionManager;
import com.utils.dbcp.ModelGenerator;

public class ProductCodeInfoServiceImpl extends AbstractService implements ProductCodeInfoService {
	private Logger logger;
	public ProductCodeInfoServiceImpl()
	{
		logger = Logger.getLogger(this.getClass());
		this.dbcp = ConfigUtil.get(Config.SECTION, Config.KEY_DBCP_NAME);
	}
	@Override
	public ProductCodeInfo getProductCodeInfoById(int id) {
		String sql = "SELECT a.id,a.productcodename,a.productid," +
				"a.spid,b.carrier,b.ordertype,b.codetype," +
				"a.spcodeid spcodeid,a.productcodeopenprovince productcodeopenprovince" +
				",a.productcodestatus productcodestatus,a.createuser createuser," +
				"a.createdate createdate,b.feelink feelink," +
				"b.entrancelink entrancelink,b.loginlink loginlink,b.sync sync" +
				",c.spname spname,a.level level FROM productcodeinfo  a left join spcode b " +
				"on a.spcodeid=b.id left join spinfo c on b.spid=c.id " +
				"where a.id="+id;
		ModelGenerator<ProductCodeInfo> dbcpmg = new ModelGenerator<ProductCodeInfo>(ProductCodeInfo.class);
		return dbcpmg.generate(dbcp, sql);
	}

	@Override
	public List<ProductCodeInfo> getProductCodeInfos() {
		String sql = "SELECT a.id id,a.productcodename productcodename,a.productid productid," +
					"a.spid spid,b.carrier carrier,b.ordertype ordertype,b.codetype codetype," +
					"a.spcodeid spcodeid,a.productcodeopenprovince productcodeopenprovince" +
					",a.productcodestatus productcodestatus,a.createuser createuser," +
					"a.createdate createdate,b.feelink feelink," +
					"b.entrancelink entrancelink,b.loginlink loginlink,b.sync sync" +
					",c.spname spname,a.level level FROM productcodeinfo  a left join spcode b " +
					"on a.spcodeid=b.id left join spinfo c on b.spid=c.id  order by a.createdate desc";
		ModelGenerator<ProductCodeInfo> dbcpmg = new ModelGenerator<ProductCodeInfo>(ProductCodeInfo.class);
		return dbcpmg.generateList(dbcp, sql);
	}

	@Override
	public List<ProductCodeInfo> getProductCodeInfos(int productId) {
		String sql = "SELECT a.id id,a.productcodename productcodename,a.productid productid," +
					"a.spid spid,b.carrier carrier,b.ordertype ordertype,b.codetype codetype," +
					"a.spcodeid spcodeid,a.productcodeopenprovince productcodeopenprovince" +
					",a.productcodestatus productcodestatus,a.createuser createuser," +
					"a.createdate createdate,b.feelink feelink," +
					"b.entrancelink entrancelink,b.loginlink loginlink,b.sync sync" +
				",c.spname spname,a.level level FROM productcodeinfo  a left join spcode b " +
				"on a.spcodeid=b.id left join spinfo c on b.spid=c.id where  a.productid="+productId;
		sql += " order by createdate desc";
		ModelGenerator<ProductCodeInfo> dbcpmg = new ModelGenerator<ProductCodeInfo>(ProductCodeInfo.class);
		return dbcpmg.generateList(dbcp, sql);
	}

	@Override
	public boolean saveProductCodeInfo(String type, ProductCodeInfo productCodeInfo,List<ProductCodeProvince> list) {
		String [] sql = null;
		if(type.equalsIgnoreCase("insert"))
		{
			sql = new String [list.size()+1];
			productCodeInfo.setId(getTableId("productcodeinfo"));
			sql[0] = "insert into productcodeinfo(id,productid,spid,spcodeid,productcodename," +
					"productcodeopenprovince,createuser,createdate,productcodestatus,level)" +
					" values ("+productCodeInfo.getId()+","+productCodeInfo.getProductId()+","+productCodeInfo.getSpId()+
					","+productCodeInfo.getSpCodeId()+",'"+productCodeInfo.getProductCodeName()+
					"','"+productCodeInfo.getProductCodeOpenProvince()+"'" +
					",'"+productCodeInfo.getCreateUser()+"','"+productCodeInfo.getCreateDate()+
					"','"+productCodeInfo.getProductCodeStatus()+"','"+productCodeInfo.getLevel()+"')";
			for(int i =0 ;i<list.size();i++)
			{
				int id = getTableId("productcodeprovince");
				ProductCodeProvince p = list.get(i);
				sql[i+1] = "insert into productcodeprovince(id,productid,productcodeid,provincecode,dailylimit," +
				"monthlylimit,dailycapacity,monthlycapacity," +
				"createuser,createdate,productcodestatus,level) values ("+(id)+","+productCodeInfo.getProductId()+","+productCodeInfo.getId()+"" +
				",'"+p.getProvinceCode()+"'" +
				",'"+p.getDailyLimit()+"','"+p.getMonthlyLimit()+"','"+p.getDailyCapacity()+"'" +
				",'"+p.getMonthlyCapacity()+"','"+productCodeInfo.getCreateDate()+"','"
				+productCodeInfo.getCreateUser()+"','"+productCodeInfo.getProductCodeStatus()+"','"+productCodeInfo.getLevel()+"')";
			}
		}else if (type.equalsIgnoreCase("update"))
		{
			sql = new String [list.size()+2];
			sql[0]="update productcodeinfo set productid='"+productCodeInfo.getProductId()+"'" +
					",spid='"+productCodeInfo.getSpId()+"'" +
					",spcodeid='"+productCodeInfo.getSpCodeId()+"'" +
					",productcodename='"+productCodeInfo.getProductCodeName()+"'" +
					",productcodeopenprovince='"+productCodeInfo.getProductCodeOpenProvince()+"'" +
					",productcodestatus='"+productCodeInfo.getProductCodeStatus()+"'" +
					",level='"+productCodeInfo.getLevel()+"'" +
					" where id ="+productCodeInfo.getId();
			
			sql[1] = "delete from productcodeprovince where productcodeid="+productCodeInfo.getId();
			
			for(int i =0 ;i<list.size();i++)
			{
				int id = getTableId("productcodeprovince");
				ProductCodeProvince p = list.get(i);
				sql[i+2] = "insert into productcodeprovince(id,productid,productcodeid,provincecode,dailylimit," +
						"monthlylimit,dailycapacity,monthlycapacity," +
						"createuser,createdate,productcodestatus,level) values ("+(id)+","+productCodeInfo.getProductId()+","+productCodeInfo.getId()+"" +
						",'"+p.getProvinceCode()+"'" +
						",'"+p.getDailyLimit()+"','"+p.getMonthlyLimit()+"','"+p.getDailyCapacity()+"'" +
						",'"+p.getMonthlyCapacity()+"','"+productCodeInfo.getCreateDate()+"','"
						+productCodeInfo.getCreateUser()+"','"+productCodeInfo.getProductCodeStatus()+"','"+productCodeInfo.getLevel()+"')";
			}
		}
		else if (type.equalsIgnoreCase("delete"))
		{
			sql = new String [2];
			if(productCodeInfo.getProductCodeStatus().equals("0"))
				productCodeInfo.setProductCodeStatus("1");
			else
				productCodeInfo.setProductCodeStatus("0");
			sql[0]="update productcodeinfo set productcodestatus='"+productCodeInfo.getProductCodeStatus()+"' where id ="+productCodeInfo.getId();
			sql[1]= "update productcodeprovince set productcodestatus='"+productCodeInfo.getProductCodeStatus()+"' where productCodeId="+productCodeInfo.getId();
		}
		else if (type.equalsIgnoreCase("restore"))
		{
			sql = new String [2];
			if(productCodeInfo.getProductCodeStatus().equals("0"))
				productCodeInfo.setProductCodeStatus("1");
			else
				productCodeInfo.setProductCodeStatus("0");
			sql[0]="update productcodeinfo set productcodestatus='"+productCodeInfo.getProductCodeStatus()+"' where id ="+productCodeInfo.getId();
			sql[1]= "update productcodeprovince set productcodestatus='"+productCodeInfo.getProductCodeStatus()+"' where productCodeId="+productCodeInfo.getId();
		}
		return ConnectionManager.getInstance().executeBatch(dbcp, sql);
	}
	@Override
	public List<ProductCodeProvince> getProductCodeProvinces(int productCodeId) {
		String sql = "select * from productcodeprovince where productcodeid="+productCodeId+" order by createdate";
		ModelGenerator<ProductCodeProvince> dbcpmg = new ModelGenerator<ProductCodeProvince>(ProductCodeProvince.class);
		return dbcpmg.generateList(dbcp, sql);
	}
	@Override
	public List<ProductCodeProvince> getProductCodeProvinces(int productId,int spCodeId) {
		String sql = "select a.id id,a.productid productid,a.productcodeid productcodeid,a.provincecode provincecode,a.dailylimit dailylimit," +
						"a.monthlylimit monthlylimit,a.dailycapacity dailycapacity,a.monthlycapacity monthlycapacity," +
						"a.createuser createuser,a.createdate createdate,a.productcodestatus productcodestatus,a.level level " +
						" from productcodeprovince a left join productcodeinfo b on a.productcodeid = b.id " +
						"where a.productid="+productId+" and b.spcodeid = "+spCodeId+"  order by createdate";
		ModelGenerator<ProductCodeProvince> dbcpmg = new ModelGenerator<ProductCodeProvince>(ProductCodeProvince.class);
		return dbcpmg.generateList(dbcp, sql);
	} 
//	public List<IProductCode> getIProductCodeAll()
//	{
//		String sql = "select c.spid spid,b.spcodeid spcodeid,b.id productCodeId" +
//				",a.id productCodeProvinceId,d.id spCodeProvinceId,b.productId productId," +
//				"e.channelid channelid,c.carrier carrier,c.orderType orderType,c.codeType codeType," +
//				"c.feeLink feeLink,c.entranceLink entranceLink,c.loginLink loginLink,c.sync sync," +
//				"a.provincecode provincecode,d.shieldregion shieldregion,d.feeValue feeValue," +
//				"a.dailyLimit dailyLimit,a.monthlyLimit monthlyLimit,a.dailyCapacity dailyCapacity," +
//				"a.monthlyCapacity monthlyCapacity,a.level level,c.pv pv  from productcodeprovince a left join " +
//				" productcodeinfo b on a.productcodeid = b.id left join spcode c " +
//				"on b.spcodeid=c.id left join spcodeprovince d on d.spCodeId=c.id and a.provincecode=d.provincecode " +
//				" left join project e on b.productId=e.id where b.productcodestatus='0'" +
//				" and c.codestatus='0' order by a.level desc";
//		ModelGenerator<IProductCode> dbcpmg = new ModelGenerator<IProductCode>(IProductCode.class);
//		return dbcpmg.generateList(dbcp, sql);
//	}
}
